﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using LightReader.Business.Entities;

namespace LightReader.Data
{
    public class UserDataAccess : IUserDataAccess
    {
        #region IUserDataAccess Members

        private string _connectionString = string.Empty;
        public UserDataAccess()
        {
            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Services.Properties.Settings.LightReaderConnectionString"].ConnectionString;
        }

        public bool DoesUserExist(string username)
        {                       
            bool returnVal = false;

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from [User] where username = @username";
                    con.Open();

                    cmd.Parameters.Add(new SqlParameter("@username", username));

                    int count = (Int32)cmd.ExecuteScalar();
                    con.Close();                    

                    if (count == 1)
                    {
                        returnVal = true;
                    }
                }
            }
            
            return returnVal;
        }

        public User GetUser(string username, string password)
        {
            User returnVal = null;

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select id, username, emailaddress from [User] where username = @username and password = @password";
                    con.Open();

                    cmd.Parameters.Add(new SqlParameter("@username", username));
                    cmd.Parameters.Add(new SqlParameter("@password", password));

                    SqlDataAdapter sa = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sa.Fill(ds);
                    DataTable dt = ds.Tables[0];

                    if (dt.Rows.Count > 0)
                    {
                        returnVal = new User();
                        returnVal.Id = System.Convert.ToInt32(dt.Rows[0]["id"].ToString());
                        returnVal.Username = dt.Rows[0]["username"].ToString();
                        returnVal.EmailAddress = dt.Rows[0]["emailaddress"].ToString();

                        DataTable feedList = getFeedsByUserId(returnVal.Id);
                        if (feedList.Rows.Count > 0)
                        {
                            returnVal.SubscribedFeeds = new List<Feed>();
                            foreach (DataRow row in feedList.Rows)
                            {
                                Feed newFeed = new Feed();
                                newFeed.Name = row[0].ToString();
                                newFeed.Url = new Uri(row[1].ToString());
                                
                                returnVal.SubscribedFeeds.Add(newFeed);
                            }
                        }
                    }

                    con.Close();
                }
            }

            return returnVal;
        }

        private DataTable getFeedsByUserId(int userID)
        {
            DataTable returnVal = null;
            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select feedTitle, feedUrl from [UserFeed] where userID = @userID order by feedTitle";
                    con.Open();

                    cmd.Parameters.Add(new SqlParameter("@userID", userID));
                    SqlDataAdapter sa = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sa.Fill(ds);
                    returnVal = ds.Tables[0];
                }
            }
            return returnVal;
        }

        public User AddUser(string username, string password, string emailAddress)
        {
            User returnVal = null;

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert into [User] (username, password, emailaddress) values(@username, @password, @emailaddress); select scope_identity()";
                    con.Open();

                    cmd.Parameters.Add(new SqlParameter("@username", username));
                    cmd.Parameters.Add(new SqlParameter("@password", password));
                    cmd.Parameters.Add(new SqlParameter("@emailaddress", emailAddress));

                    object newID = cmd.ExecuteScalar();
                    con.Close();

                    if(newID != null) 
                    {
                        returnVal = new User();
                        returnVal.Id = System.Convert.ToInt32(newID);
                        returnVal.Username = username;
                        returnVal.EmailAddress = emailAddress;
                    }
                }
            }

            return returnVal;
        }

        public bool AddFeed(int userID, string feedTitle, string feedUrl)
        {
            bool returnVal = false;

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert into UserFeed(UserId, FeedTitle, FeedUrl) values(@userID, @feedTitle, @feedUrl)";
                    con.Open();

                    cmd.Parameters.Add(new SqlParameter("@userID", userID));
                    cmd.Parameters.Add(new SqlParameter("@feedTitle", feedTitle));
                    cmd.Parameters.Add(new SqlParameter("@feedUrl", feedUrl));

                    int ra = cmd.ExecuteNonQuery();
                    if (ra == 1)
                    {
                        returnVal = true;
                    }

                    con.Close();
                }
            }
            return returnVal;
        }

        #endregion
    }
}
